package jdbc.data;

import jdbc.db.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDao {
    public boolean createTable(){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs=null;
        try {
            conn= DBUtils.getConnectionByC3P0();
            ps=conn.prepareStatement("CREATE TABLE `user` (\n" +
                    "  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,\n" +
                    "  `username` varchar(255) NOT NULL,\n" +
                    "  `password` varchar(255) NOT NULL,\n" +
                    "  `picture` blob,\n" +
                    "  PRIMARY KEY (`id`)\n" +
                    ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                DBUtils.close(conn,ps,rs);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return true;
    }

    public int inert(User user){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs=null;
        int v=0;
        try {
            conn= DBUtils.getConnectionByC3P0();
            ps=conn.prepareStatement("insert into user(username,password)VALUES(?,?)");
            ps.setString(1,user.getUsername());
            ps.setString(2,user.getPassword());

            v = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                DBUtils.close(conn,ps,rs);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return v;
    }

    public List<User> selectAll(){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs=null;
        List<User> users=new ArrayList<>();
        try {
            conn= DBUtils.getConnectionByC3P0();
            ps=conn.prepareStatement("select username,password from user");
            rs=ps.executeQuery();
            while (rs.next()){
                User user=new User(rs.getString("username"),rs.getString("password"));
                users.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                DBUtils.close(conn,ps,rs);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return users;
    }

    public static void main(String[] args) {
        String path="/U/s/data/user.sql";
        System.out.println(path.substring(path.lastIndexOf("/")+1,path.lastIndexOf(".")));
    }
}
